﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

using EHTWebsiteProject.Code.Entity;
using EHTWebsite.Code;


namespace EHTWebsiteProject.Code.Entity
{
    public class OrderProvider
    {
        #region Quản lý Order

        public static Int32 UIOrder(Order theOrder, Boolean isAdd)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_IUOrder";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter orderIDOutParam = new SqlParameter("@OrderIDOut", System.Data.OleDb.OleDbType.Integer);
            orderIDOutParam.Direction = ParameterDirection.Output;

            command.Parameters.Add(orderIDOutParam);
            command.Parameters.Add(new SqlParameter("@OrderID", theOrder.OrderID));
            command.Parameters.Add(new SqlParameter("@CustomerID", theOrder.CustomerID));
            command.Parameters.Add(new SqlParameter("@RequireDate", theOrder.RequireDate));
            command.Parameters.Add(new SqlParameter("@ReceivedName", theOrder.ReceivedName));
            command.Parameters.Add(new SqlParameter("@ReceivedAddress", theOrder.ReceivedAddress));
            command.Parameters.Add(new SqlParameter("@ReceivedCity", theOrder.ReceivedCity));
            command.Parameters.Add(new SqlParameter("@ReceivedRegionID", theOrder.ReceivedRegionID));
            command.Parameters.Add(new SqlParameter("@ReceivedPostalCode", theOrder.ReceivedPostalCode));
            command.Parameters.Add(new SqlParameter("@ReceicedPhone", theOrder.ReceicedPhone));
            command.Parameters.Add(new SqlParameter("@OrderState", theOrder.OrderState));
            command.Parameters.Add(new SqlParameter("@TotalAmount", theOrder.TotalAmount));
            command.Parameters.Add(new SqlParameter("@ServiceCost", theOrder.ServiceCost));
            command.Parameters.Add(new SqlParameter("@IsAdd", isAdd));
            DataProvider.ExecuteNoneQuery(command);

            return Int32.Parse(command.Parameters["@OrderIDOut"].Value.ToString());
        }

        public static void UpdateShipAndBillOfOrderByOrderID(Int32 OrderID, int OrderState, String BillPictureUrl, int IsShip)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_UpdateShipAndBillOfOrderByOrderID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@OrderID", OrderID));
            command.Parameters.Add(new SqlParameter("@OrderState", OrderState));
            command.Parameters.Add(new SqlParameter("@BillPictureUrl", BillPictureUrl));
            command.Parameters.Add(new SqlParameter("@IsShip", IsShip));
            DataProvider.ExecuteNoneQuery(command);
        }

        public static void DeleteOderByOrderID(Int32 orderID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_DeleteOrderByOrderID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@OrderID", orderID));
            DataProvider.ExecuteNoneQuery(command);
        }

        public static Order GetOrderByOrderID(Int32 orderID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetOrderByOrderID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@OrderID", orderID));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            Order theOrder = null;

            if (ds.Tables[0].Rows.Count > 0)
            {
                theOrder = new Order();
                theOrder.BillPictureUrl = ds.Tables[0].Rows[0]["BillPictureUrl"].ToString();
                theOrder.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreatedDate"].ToString());
                theOrder.CustomerID = ds.Tables[0].Rows[0]["CustomerID"].ToString();
                theOrder.OrderID = Int32.Parse(ds.Tables[0].Rows[0]["OrderID"].ToString());
                theOrder.OrderState = Int32.Parse(ds.Tables[0].Rows[0]["OrderState"].ToString());
                theOrder.ReceicedPhone = ds.Tables[0].Rows[0]["ReceicedPhone"].ToString();
                theOrder.ReceivedAddress = ds.Tables[0].Rows[0]["ReceivedAddress"].ToString();
                theOrder.ReceivedCity = ds.Tables[0].Rows[0]["ReceivedCity"].ToString();
                theOrder.ReceivedName = ds.Tables[0].Rows[0]["ReceivedName"].ToString();
                theOrder.ReceivedPostalCode = ds.Tables[0].Rows[0]["ReceivedPostalCode"].ToString();
                theOrder.ReceivedRegionID = Int32.Parse(ds.Tables[0].Rows[0]["ReceivedRegionID"].ToString());
                theOrder.TotalAmount = float.Parse(ds.Tables[0].Rows[0]["TotalAmount"].ToString());
                theOrder.ServiceCost = float.Parse(ds.Tables[0].Rows[0]["ServiceCost"].ToString());
                try
                {
                    theOrder.RequireDate = DateTime.Parse(ds.Tables[0].Rows[0]["RequireDate"].ToString());
                }
                catch (Exception)
                {

                }
                try
                {
                    theOrder.ShipDate = DateTime.Parse(ds.Tables[0].Rows[0]["ShippedDate"].ToString());
                }
                catch (Exception)
                {


                }
                try
                {
                    theOrder.UploadedDateOfBill = DateTime.Parse(ds.Tables[0].Rows[0]["UploadedDateOfBill"].ToString());
                }
                catch (Exception)
                {

                }

            }

            return theOrder;
        }

        public static DataSet GetOrdersByCustomerID(String customerID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetOrdersByCustomerID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@CustomerID", customerID));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            return ds;
        }

        public static DataSet GetOrdersByCustomerIDAndOrderState(Int32 pageIndex, Int32 pageSize, out Int32 totalRecord, String customerID, Int32 OrderState)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetOrdersByOrderStateAndCustomerID";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@OrderState", OrderState));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            command.Parameters.Add(new SqlParameter("@CustomerID", customerID));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }

        public static DataSet GetOrdersForOrderState(String customerID, Int32 OrderState)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetOrdersForOrderState";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@CustomerID", customerID));
            command.Parameters.Add(new SqlParameter("@OrderState", OrderState));
            DataSet ds = DataProvider.ExecuteDataSet(command);
            return ds;
        }
        #endregion

        #region BLL For Admin

        public static DataSet GetOrdersByOrderState(Int32 orderState, Int32 pageIndex, Int32 pageSize, out Int32 totalRecord)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetOrdersByOrderState";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@OrderState", orderState));
            command.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            DataSet ds = DataProvider.ExecuteDataSet(command, out totalRecord);
            return ds;
        }


        //Ham lay du lieu exprot ta CVS
        //Start

        public static DataSet GetOrdersExportCVS()
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetOrdersExportCVS";
            command.CommandType = CommandType.StoredProcedure;

            return DataProvider.ExecuteDataSet(command);
        }
        //End

        public static void GetParametersOfOrder(out Int32 orderAppendingTotal, out Int32 orderShippingTotal)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetParametersOfOrder";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter orderAppendingOutParam = new SqlParameter("@OrderAppendingParmeter", System.Data.OleDb.OleDbType.Integer);
            orderAppendingOutParam.Direction = ParameterDirection.Output;
            SqlParameter orderShippingOutParam = new SqlParameter("@OrderShippingParmeter", System.Data.OleDb.OleDbType.Integer);
            orderShippingOutParam.Direction = ParameterDirection.Output;
            command.Parameters.Add(orderAppendingOutParam);
            command.Parameters.Add(orderShippingOutParam);
            DataProvider.ExecuteNoneQuery(command);

            orderAppendingTotal = Int32.Parse(command.Parameters["@OrderAppendingParmeter"].Value.ToString());
            orderShippingTotal = Int32.Parse(command.Parameters["@OrderShippingParmeter"].Value.ToString());
        }

        public static void GetParametersOfOrderByCustomerID(out Int32 orderAppendingTotal, out Int32 orderTotal, string CustomerID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "proc_GetParametersOfOrderByCustomerID";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter orderAppendingOutParam = new SqlParameter("@OrderAppendingParmeter", System.Data.OleDb.OleDbType.Integer);
            orderAppendingOutParam.Direction = ParameterDirection.Output;
            SqlParameter orderShippingOutParam = new SqlParameter("@AllOrderParmeter", System.Data.OleDb.OleDbType.Integer);
            orderShippingOutParam.Direction = ParameterDirection.Output;
            command.Parameters.Add(orderAppendingOutParam);
            command.Parameters.Add(orderShippingOutParam);
            command.Parameters.Add(new SqlParameter("@CustomerID", CustomerID));
            DataProvider.ExecuteNoneQuery(command);

            orderAppendingTotal = Int32.Parse(command.Parameters["@OrderAppendingParmeter"].Value.ToString());
            orderTotal = Int32.Parse(command.Parameters["@AllOrderParmeter"].Value.ToString());
        }
        #endregion
    }
}